id | title | description | subtitle |
---|---|---|---|
debugging-performance |
Debugging performance issues |
Debug slow-running queries using the Postgres execution planner. |
Debug slow-running queries using the Postgres execution planner. |
explain()
is a method that provides the Postgres EXPLAIN
execution plan of a query. It is a powerful tool for debugging slow queries and understanding how Postgres will execute a given query. This feature is applicable to any query, including those made through rpc()
or write operations.
explain()
is disabled by default to protect sensitive information about your database structure and operations. We recommend using explain()
in a non-production environment. Run the following SQL to enable explain()
:
{/* prettier-ignore */}
-- enable explain
alter role authenticator
set pgrst.db_plan_enabled to 'true';
-- reload the config
notify pgrst, 'reload config';
To get the execution plan of a query, you can chain the explain()
method to a Supabase query:
{/* prettier-ignore */}
const { data, error } = await supabase
.from('countries')
.select()
.explain()
To illustrate, consider the following setup of a countries
table:
{/* prettier-ignore */}
create table countries (
id int8 primary key,
name text
);
insert into countries
(id, name)
values
(1, 'Afghanistan'),
(2, 'Albania'),
(3, 'Algeria');
The response would typically look like this:
{/* prettier-ignore */}
Aggregate (cost=33.34..33.36 rows=1 width=112)
-> Limit (cost=0.00..18.33 rows=1000 width=40)
-> Seq Scan on countries (cost=0.00..22.00 rows=1200 width=40)
By default, the execution plan is returned in TEXT format. However, you can also retrieve it as JSON by specifying the format
parameter.
If you need to enable explain()
in a production environment, ensure you protect your database by restricting access to the explain()
feature. You can do so by using a pre-request function that filters requests based on the IP address:
{/* prettier-ignore */}
create or replace function filter_plan_requests()
returns void as $$
declare
headers json := current_setting('request.headers', true)::json;
client_ip text := coalesce(headers->>'cf-connecting-ip', '');
accept text := coalesce(headers->>'accept', '');
your_ip text := '123.123.123.123'; -- replace this with your IP
begin
if accept like 'application/vnd.pgrst.plan%' and client_ip != your_ip then
raise insufficient_privilege using
message = 'Not allowed to use application/vnd.pgrst.plan';
end if;
end; $$ language plpgsql;
alter role authenticator set pgrst.db_pre_request to 'filter_plan_requests';
notify pgrst, 'reload config';
Replace '123.123.123.123'
with your actual IP address.
To disable the explain()
method after use, execute the following SQL commands:
{/* prettier-ignore */}
-- disable explain
alter role authenticator
set pgrst.db_plan_enabled to 'false';
-- if you used the above pre-request
alter role authenticator
set pgrst.db_pre_request to '';
-- reload the config
notify pgrst, 'reload config';